import openpyxl
from config import users_list, exclusions


def writeExcel(file, sheet_index):
    workbook = openpyxl.load_workbook(file)
    worksheet = workbook.worksheets[sheet_index]
    nrows = worksheet.max_row
    ncols = worksheet.max_column
    print(nrows, ncols)

    worksheet.cell(1, 11).value = '主负责人'
    worksheet.cell(1, 12).value = '部门'
    worksheet.cell(1, 13).value = '存量/新增'
    exclusion_lists = []
    for i in range(len(exclusions)):
        exclusion_lists.append(exclusions[i].get('clientid'))
    # print(exclusion_lists)

    for row in range(2, nrows+1):
        email = worksheet.cell(row, 7).value
        if email == None:
            clientids = worksheet.cell(row, 2).value
            # print('clientids', clientids)
            clientids_list = str(clientids).split(',')
            # print('clientids_list', clientids_list)
            new_clientids_list = []
            for i in clientids_list:
                if i not in exclusion_lists:
                    new_clientids_list.append(i)
            new_clientids_list.reverse()
            print(new_clientids_list[0])
            for i in range(len(users_list)):
                if new_clientids_list[0] in users_list[i].get('clientids'):
                    worksheet.cell(row, 7).value = users_list[i].get('username')
                    worksheet.cell(row, 11).value = users_list[i].get('owner')
                    worksheet.cell(row, 12).value = users_list[i].get('department')
                    worksheet.cell(row, 13).value = '新增'
                    print(users_list[i].get('owner'))
                    print('-----------------------------')
        else:
            for i in range(len(users_list)):
                print()
                if email == users_list[i].get('email'):
                    worksheet.cell(row, 12).value = users_list[i].get('department')
                    worksheet.cell(row, 13).value = '存量'
    workbook.save(file)


if __name__ == '__main__':
    # file = 'log_2021-04-17_2021-04-20_sensitivedata.xlsx'
    file = 'log_2021-05-21_2021-05-25_sensitivedata.xlsx'
    # data = get_data(file, 0)
    writeExcel = writeExcel(file, 4)
